<?php

namespace app\index\model;

use  think\Db;

class CountryonwayModel
{
    private static $salesState=["1"=>"新产品","2"=>"清仓品","3"=>"在售品","4"=>"下架品"];  //销售状态
    private static $cycleStage=["1"=>"补货期","2"=>"新品期","3"=>"计划淘汰期","4"=>"淘汰期"];  //生命周期

    //用户列表
    static public function getList($param, $titles = null, $function = null)
    {
        $where=[];$condition=[];
        $build=Db::table('ship_plan_detail')->alias('d')->join('ship_plan p' ,'p.id = ship_plan_id','left')->field('concat(d.code,"_",sku,"_",shop_id) as skucodeshop,d.sku_name,d.unit_price,p.warehouse_id_out,p.warehouse_id_rec')->buildSql();

        $db = Db::table('ship_plan_send')->alias('s')->join("$build b" ,'concat(code,"_",sku,"_",shop_id) = b.skucodeshop','left')
                ->field('b.sku_name,b.unit_price,b.warehouse_id_out,s.code,sum(s.quantity) quantity,s.date,s.sku')->group('concat(s.code,"_",s.sku)');
        
     /*   $sqlBuild=Db::table('warehouse_country_detail')->alias('cd')->join('warehouse_country c' ,'cd.warehouse_country_id = c.id','left')
               ->join('ship_plan_logistics l' ,'l.code = c.bs_code','left')->field('bs_code,')->buildSql();
        $db->join("$sqlBuild s" ,'d.id = p.ship_plan_id','left');*/
        if (!empty($param['sku'])) {
            $where['s.sku']=$param['sku'];
        }
        if (!empty($param['warehouse_id_out'])) {
            $where['warehouse_id_out']=$param['warehouse_id_out'];
        }        
        if (!empty($param['code'])) {
            $where['s.code']=$param['code'];
        }
        $out=$db->where($where)->buildSql();
        $warehouse=Db::table('warehouse_country_detail')->alias('cd')->join('warehouse_country c' ,'cd.warehouse_country_id = c.id','left')
                 ->field('sum(quantity) warequantity,date as waredate,bs_code,sku,receipt_warehouse_id')->group('concat(bs_code,"_",sku)');
        $condition['status']=20;
        $condition['type']=10;
        $sqlBuild=$warehouse->where($condition)->buildSql();
        $dbObj=Db::table("$out")->alias('o')->join("$sqlBuild sd" ,'concat(o.code,"_",o.sku)= concat(sd.bs_code,"_",sd.sku)','left')->field('o.*,warequantity,waredate,bs_code,sd.receipt_warehouse_id');
        if (!empty($param['warehouse_id_rec'])) {
            $dbObj->where('receipt_warehouse_id','=',$param['warehouse_id_rec']);
        }
       
        return \app\common\service\ToolService::downOrReturn($dbObj, $param, $titles,$function);
    }
}
